﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;

namespace IntelligentCMS
{
    /// <summary>
    /// The DataLayer class, for abstracting data access away from the actual database.
    /// 
    /// </summary>
    public class CMSDbDataLayer
    {

        /// <summary>
        /// Initializes a new instance of the <see cref="CMSDbDataLayer"/> class.
        /// </summary>
        /// <param name="connectionstring">The connection string.</param>
        /// <param name="dbtype">The database type.</param>
        public CMSDbDataLayer(string dbconnectionstring, string dbtype) 
        {
            this.dbconnectionstring = dbconnectionstring;
            this.dbtype = dbtype;


        }
        private string dbconnectionstring = string.Empty;
        private string dbtype = string.Empty;
        private string sqlquery = string.Empty;
        private DataTable data = new DataTable();

        private bool selectquery = true;

        /// <summary>
        /// Executes the query. If it's a non-query, aka an UPDATE or INSERT statement, we don't need to worry about the data.
        /// If it's a SELECT statement, we do.
        /// </summary>
        public void Execute()
        {
            CMSDbDataHelper newHelper = new CMSDbDataHelper(sqlquery, dbtype, dbconnectionstring);
            newHelper.SelectQuery = selectquery;
            newHelper.Execute();

            if (selectquery)
            {
                data = newHelper.Data;
            }

        }

        // Temporary bypass.
        public void AddQuery(string sqlquery)
        {
            this.sqlquery += sqlquery + ";";

        }


        public void AddTable(List<string> columns, List<string> datatypes, string table)
        {
            sqlquery += "CREATE TABLE " + table + " (";

            for(int i = 0; i < columns.Count; i++) 
            {
                if (i == 0)
                {
                    sqlquery += columns[i] + " " + datatypes[i];
                }
                else
                {
                    sqlquery += ", " + columns[i] + " " + datatypes[i];
                }
            }

            sqlquery += ");";
        }

        public void AddRow(List<string> columns, List<string> data, string table)
        {

            sqlquery += "INSERT INTO " + table + " (";

            for (int i = 0; i < columns.Count; i++)
            {
                if (i == 0)
                {
                    sqlquery += columns[i];
                }
                else
                {
                    sqlquery += ", " + columns[i];
                }
            }

            sqlquery += ") VALUES (";

            for (int i = 0; i < data.Count; i++)
            {
                if (i == 0)
                {
                    sqlquery += data[i];
                }
                else
                {
                    sqlquery += ", " + data[i];
                }
            }

            sqlquery += ");";



        }

        public void DeleteTable(string table)
        {
            sqlquery += "DROP TABLE " + table + ";";
        }


        // We will need to unify the ID columns. Right now they have different names,
        // so might need to change them, but I have an idea.
        public void DeleteRow(string rowid, string column, string table)
        {
            sqlquery += "DELETE FROM " + table + " WHERE " + column + " = " + rowid + ";";
        }

        public void DeleteAllRows(string table)
        {
            sqlquery += "DELETE FROM " + table + ";";
        }

        public void UpdateRow(string rowid, string column, string table, List<string> columns, List<string> data) 
        {
            sqlquery += "UPDATE " + table + " SET ";

            for (int i = 0; i < columns.Count; i++)
            {
                if (i == 0)
                {
                    sqlquery += columns[i] + "=" + data[i];
                }
                else
                {
                    sqlquery += ", " + columns[i] + "=" + data[i];
                }
            }

            sqlquery += " WHERE " + column + " = " + rowid + ";";


        }

        public void CreateDB(string database)
        {
            sqlquery += "CREATE DATABASE " + database + ";";

        }

        public void DeleteDB(string database)
        {
            sqlquery += "DROP DATABASE " + database + ";";

        }

        public void CreateIndex(string table, string column)
        {
            sqlquery += "CREATE INDEX " + column + "_INDEX ON " + table + " (" + column + ");"; 

        }




        /// <summary>
        /// Sets a value indicating whether  it is a non-query (like UPDATE or INSERT).
        /// </summary>
        /// <value>
        ///   <c>true</c> if [non query]; otherwise, <c>false</c>.
        /// </value>
        public bool SelectQuery
        {
            set
            {
                selectquery = value;
            }

        }

        /// <summary>
        /// Gets the data, in the form of a datatable. We could switch to an XMLDataDocument, for maximum compatability.
        /// </summary>
        public DataTable Data
        {
           get 
           {
                return data;
           }

        }
 

    }
}
